<?php

namespace app\store\model;

use think\Model;

class UserSignLogModel extends Model
{
    protected $connection = 'db_platform_log';

    protected $table = 'user_score_log';

    const SIGN_IN_TYPE = '月签到';//月签到
    const LOGIN_TYPE = '进入大厅';//登录游戏的人

    // 时间类型
    protected $date_type = null;

    // 搜索的时间
    protected $date_range = null;

    // 搜索的用户ids(注册或者登录)
    protected $search_user_ids = [];

    // 充值的用户ids
    protected $paid_user_ids = [];

    // 筛选的用户字段和值
    protected $user_field = null;
    protected $user_value_start = null;
    protected $user_value_end = null;

    public function __construct($data = [])
    {
        parent::__construct($data);
        $time_field = request()->get('time_field');
        $time_value = request()->get('time_value');
        $this->user_field = request()->get('user_field');
        $this->user_value_start = request()->get('user_value_start');
        $this->user_value_end = request()->get('user_value_end');
        // 获取具体时间
        if (!empty($time_field) and !empty($time_value)) {
            $list = explode(' - ', $time_value);
            $begin = $list[0];
            $after = $list[1];
        } else {
            $begin = $after = date('Y-m-d');
        }
        $this->date_range = [$begin, $after . ' 23:59:59'];

        $this->date_type = !empty($time_field) ? $time_field : 'RegisterDate';
    }


    /**获取签到统计
     * @param $where
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getSignIn($where)
    {
        // 判断是否传了用户筛选 如果没有就显示所有的 直接查汇总表

        if ((!empty($this->user_value_start) and !empty($this->user_value_end))
            and ($this->user_value_start != 0 and $this->user_value_end != 0)
        ) {
            if ($this->user_field == 'cannon') {
                //签到人数
                $query = self::where($where)
                    ->fieldRaw('left(l.RecordTime,10) as time,count(l.UserID) as number')
                    ->alias('l')
                    ->where('l.type', self::SIGN_IN_TYPE)
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->leftJoin('qpaccountsdb.user_game_info g', 'l.UserID = g.UserID')
                    ->group('time');
                // 新增还是活跃
                if ($this->date_type == 'RegisterDate') {
                    // 新增直接查用户表 不用关联
                    $query2 = self::table('qpaccountsdb.accounts_info a')
                        ->fieldRaw('left(a.RegisterDate,10) as time,count(a.UserID) as number')
                        ->where($where)
                        ->group('a.RegisterDate')
                        ->leftJoin('qpaccountsdb.user_game_info g', 'a.UserID = g.UserID')
                        ->where('a.RegisterDate', 'between', $this->date_range);
                } else {
                    $query2 = self::table('qplogdb.user_inout_log l')
                        ->where($where)
                        ->fieldRaw('left(l.RecordTime,10) as time,count(distinct(l.UserID)) as number')
                        ->alias('l')
                        ->where('l.type', self::LOGIN_TYPE)
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->leftJoin('qpaccountsdb.user_game_info g', 'l.UserID = g.UserID')
                        ->group('time');
                }
            } else {
                //签到人数
                $query = self::where($where)
                    ->fieldRaw('left(l.RecordTime,10) as time,count(distinct(l.UserID)) as number')
                    ->alias('l')
                    ->where('l.type', self::SIGN_IN_TYPE)
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->group('time')
                    ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID');
                // 新增还是活跃
                if ($this->date_type == 'RegisterDate') {
                    // 新增直接查用户表 不用关联
                    $query2 = self::table('qpaccountsdb.accounts_info a')
                        ->fieldRaw('left(a.RegisterDate,10) as time,count(a.UserID) as number')
                        ->where($where)
                        ->group('time')
                        ->where('a.RegisterDate', 'between', $this->date_range);
                } else {
                    $query2 = self::table('qplogdb.user_inout_log l')->where($where)
                        ->fieldRaw('left(l.RecordTime,10) as time,count(distinct(l.UserID)) as number')
                        ->alias('l')
                        ->where('l.type', self::LOGIN_TYPE)
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->group('time')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID');
                }
            }
        } else {

            $sub_sql = '';

            // 新增还是活跃
            if ($this->date_type == 'RegisterDate') {
                // 新增直接查用户表 不用关联
                $query2 = self::table('qpaccountsdb.accounts_info a')
                    ->where($where)
                    ->where('a.RegisterDate', 'between', $this->date_range);
                $query2_clone = clone $query2;

                $query2 = $query2
                    ->fieldRaw('left(a.RegisterDate,10) as time,count(a.UserID) as number')
                    ->group('time');

                $sub_sql = $query2_clone
                    ->fieldRaw('a.UserID')
                    ->buildSql();
            } else {
                $query2 = self::table('qplogdb.user_inout_log l')
                    ->fieldRaw('left(l.RecordTime,10) as time,count(distinct(l.UserID)) as number')
                    ->alias('l')
                    ->where($where)
                    ->where('l.type', self::LOGIN_TYPE)
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->group('time');
            }


            // 签到人数
            $query = self::fieldRaw('left(l.RecordTime,10) as time,count(l.UserID) as number')
                ->alias('l')
                ->where('l.type', self::SIGN_IN_TYPE)
                ->where('l.RecordTime', 'between', $this->date_range)
                ->group('time');
            if($sub_sql != ''){
                $query->where('l.UserID in '.$sub_sql);
            }
        }

        $data = $query->select()->toArray();
        $data2 = $query2->select()->toArray();

        // 合并数组
        foreach ($data as $k => $v) {
            $data[$k]['number2'] = isset($data2[$k]['number']) ? $data2[$k]['number'] : 0;
            $data[$k]['rate'] = isset($data2[$k]['number']) ? round($data[$k]['number'] / $data2[$k]['number'], 2) : 0;
        }

        unset($data2);

        return $data;
    }

}